[Mysql] mysql-show table status语法


本文总阅读量次

1、使用方法：

1
2
3
show table status from aaaa like 'test_order';
或
show table status in aaaa like 'test_order';

结果显示如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
*************************** 1. row ***************************
           Name: test_order
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 84052104
 Avg_row_length: 372
    Data_length: 31321817088
Max_data_length: 0
   Index_length: 25528303616
      Data_free: 7340032
 Auto_increment: 86709693
    Create_time: 2018-11-02 16:20:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

2、具体参数详解：

1
Name:表名
1
Engine:表使用的存储引擎
1
Version：该列在8.0版本已经废弃，在5.7版本下显示硬编码10
1
Row_format：行存储格式（Fixed, Dynamic, Compressed, Redundant, Compact）。对于myisam表，Dynamic值与 myisamchk -dvv查询出来的Packed值相对应。
1
Rows:对于myisam存储引擎，显示实际行数。对于其他引擎，比如innodb，值是近似的(粗略估计），和实际值可能相差40%至50%之间，想要获取准确的值使用select count(*)。
1
Avg_row_length：平均行长度。
1
Data_length：对于Myisam表，该值以字节为单位显示数据文件的长度；对于Innodb,该值以字节为单位显示为聚簇索引分配内存的值（近似）。
1
Max_data_length：对于Myisam表，该值显示数据文件的最大长度。这是可以存储在表中的数据的总字节数，该表给定使用的数据指针大小。对于Innodb，该值已弃用。
1
2
Index_length：对于Myisam，该值为索引文件的长度（以字节为单位）。对于Innodb，该值显示内存为非聚簇索引分配的数量（以字节为单位），它是内存页中非聚簇索引大小的总和与
Innodb内存页大小的乘积。
1
2
3
4
Data_free：已分配但未使用的字节数。Innodb-tables代表当前表所属表空间的可用空间。如果该表位于共享表空间，该值代表共享表空间的剩余空间。如果使用多个表空间，每个表拥有自己的表空间，该值仅代表当前表的剩余空间。剩余空间的意思就是当前完全空闲的区字节数减去一个安全的边界值。即使该值为0，只要新的区可以被分配插入数据也是正常的。
对于NDB集群，该值显示磁盘分配的空间，但是还没有被磁盘数据表或者磁盘数据碎片使用的空间。
对于分区表，该值只是估计值并不准确。想要获取更加准确的方法使用下面方法查询：
SELECT SUM(DATA_FREE) FROM  INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';
1
Auto_increment：下一个自动增长值。
1
Create_time：表创建时间。
1
2
Update_time：数据文件最后更新时间。对于一些存储引擎，该是为null。例如，Innodb存储多个表在系统表空间而且数据文件时间戳没有应用。Innodb表是使用file-per-table模式，每个表都有他自己的.ibd文件，内存buffer写入数据文件存在延迟，因而文件修改时间与最后的插入、更新、删除修改时间是不同的。对于Myisam表，数据文件时间戳是可用的，但是在window系统上，更新并不会更新时间戳，因此该值是不准确的。
对于非分区的Innodb表，该值显示最后update、insert、delete的时间戳的值。对于MVCC，时间戳的值代表commit的时间。如果服务器重启或者该表被数据字典缓存清除，时间戳不会保留。
1
Check_time：表最后一次检查时间。
1
Collation：表默认的排序规则。
1
Checksum：校验值。
1
2
Create_options：创建表时的额外选项。当执行create table时被保留的原始选项，这些选项与活动表的设置和选项不同。
对于Innodb表，该值显示row_format和key_block_size选项。如果表示分区表，该值会显示partitioned。当创建或者修改一个file-per-table表空间为加密模式时，该值会显示为ENCRYPTIONG(general tablespace除外)。
1
Comment：创建表空间添加的注释。

3、测试对innodb表添加encryption功能：

3.1、安装插件

1
INSTALL PLUGIN keyring_file  soname 'keyring_file.so';

3.2、创建密钥文件目录

1
2
mkdir -p /data/mysql/keyfile
chown mysql.mysql -R /data/mysql/keyfile

3.3、设置key文件：

1
2
3
4
5
6
7
8
9
set global keyring_file_data='/data/mysql/keyfile/key01';
show global variables like '%keyring_file_data%';
root@db 16:28:  [aaaa]> show global variables like '%keyring_file_data%';
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| keyring_file_data | /data/mysql/keyfile/key01 |
+-------------------+---------------------------+
1 row in set (0.01 sec)

3.4、查看插件状态：

1
2
3
4
5
6
7
8
9
root@db 16:27:  [aaaa]> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.01 sec)

3.5、表aa未加密时，查看表状态：

1
2
3
4
5
6
7
root@db 16:29:  [aaaa]> show table status from aaaa like 'aa';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| aa   | InnoDB |      10 | Dynamic    |   12 |           1365 |       16384 |               0 |            0 |         0 |           NULL | 2018-10-29 17:30:47 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

3.6、对表aa进行加密：

1
2
3
root@db 16:29:  [aaaa]> alter table aa encryption='Y';
Query OK, 12 rows affected (5.13 sec)
Records: 12  Duplicates: 0  Warnings: 0

3.7、再次查看表aa状态：

1
2
3
4
5
6
7
root@db 16:30:  [aaaa]> show table status from aaaa like 'aa';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| aa   | InnoDB |      10 | Dynamic    |   12 |           1365 |       16384 |               0 |            0 |         0 |           NULL | 2018-11-14 16:30:16 | 2018-11-14 16:30:14 | NULL       | utf8mb4_general_ci |     NULL | ENCRYPTION="Y" |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)

4、下面为大佬提供的方便查询数据库下所有表所占行数（估计）和占用空间的存储过程，点此跳转至原文，具体内容如下：

4.1、存储过程所在的aaaa数据库，可根据自己数据库情况自行修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
DELIMITER $$
DROP PROCEDURE IF EXISTS `aaaa`.`sp_status` $$
CREATE PROCEDURE `aaaa`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

4.2、执行存储过程：

1
call aaaa.sp_status('aaaa');

4.3、结果如下：

1
2
3
4
5
6
7
8
9
10
root@db 16:58:  [aaaa]> call aaaa.sp_status('aaaa');
+--------------+--------+----------+--------------+--------------------+
| Table Name   | Engine | Rows     | Size         | Collation          |
+--------------+--------+----------+--------------+--------------------+
| aa           | InnoDB | 12       | 0.02 Mb      | utf8mb4_general_ci |
| sequence     | InnoDB | 12292293 | 515.98 Mb    | utf8mb4_general_ci |
| test         | InnoDB | 0        | 0.02 Mb      | utf8mb4_general_ci |
| test1        | InnoDB | 6        | 0.02 Mb      | utf8mb4_general_ci |
| test_order  | InnoDB | 84052104 | 54,216.50 Mb | utf8mb4_general_ci |
+--------------+--------+----------+--------------+--------------------+
mysql
目录
  1. 1. 1、使用方法：
  2. 2. 2、具体参数详解：
  3. 3. 3、测试对innodb表添加encryption功能：
  4. 4. 4、下面为大佬提供的方便查询数据库下所有表所占行数（估计）和占用空间的存储过程，点此跳转至原文，具体内容如下：

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog